﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class BidDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public BidDAL()
        {

        }

        public IList<BidInfo> GetBid(Nullable<int> YearTypeSeq, Nullable<int> Year, string RecordStatus)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidInfo> infoList = new List<BidInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@year_type_seq", YearTypeSeq);
                dbManager.AddParameters(2, "@bgm_year", Year);
                dbManager.AddParameters(3, "@record_status", RecordStatus);

                strSQL = "SELECT b.*"
                            + ", CONCAT(pm.ICM_PC_METHOD_NAME , pt.ICM_PC_TYPE_NAME) AS BID_TYPE"
                            + ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', b.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                            + ", rs.RECORD_STATUS_NAME"
                        + " FROM ICM_BID b"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON b.RECORD_STATUS = rs.RECORD_STATUS"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON b.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON b.ICM_PR_SEQ = pr.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pt.ICM_PC_TYPE_SEQ = pr.ICM_PC_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pm.ICM_PC_METHOD_SEQ = pr.ICM_PC_METHOD_SEQ"
                        + " WHERE CASE WHEN @record_status = '' THEN b.RECORD_STATUS <> @delete_status ELSE b.RECORD_STATUS = @record_status END"
                            + " AND CASE WHEN @year_type_seq = NULL THEN 0 = 0 ELSE b.YEAR_TYPE_SEQ = @year_type_seq END"
                            + " AND CASE WHEN @bgm_year = NULL THEN 0 = 0 ELSE b.BGM_YEAR = @bgm_year END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidInfo info = new BidInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_BID_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_BID_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_BID_NAME"]).ToString();
                        info.Descr = (dr[i]["ICM_BID_DESCR"]).ToString();
                        info.BidType = (dr[i]["BID_TYPE"]).ToString();
                        info.YearTypeDescr = (dr[i]["YEAR_TYPE_DESCR"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        info.DateBid = (Convert.IsDBNull(dr[i]["BID_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["BID_DATE"]));
                        info.DateShow = String.Format("{0:dd/MM/yyyy}", info.DateBid);
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));

                        info.InfoYearType = infoYearType;
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public BidInfo GetBid(int Seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            BidInfo info = new BidInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@bid_seq", Seq);

                strSQL = "SELECT b.*"
                            + ", pr.ICM_PR_NAME"
                            + ", pr.ICM_PR_REF_NO"
                            + ", pr.BGM_YEAR AS PR_BGM_YEAR"
                            + ", pr.ICM_PC_RULE_SEQ AS PR_ICM_PC_RULE_SEQ"
                            + ", ru.ICM_PC_RULE_NAME"
                            + ", pr.YEAR_TYPE_SEQ AS PR_YEAR_TYPE_SEQ"
                            + ", yt.YEAR_TYPE_NAME"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", CONCAT(pm.ICM_PC_METHOD_NAME, pt.ICM_PC_TYPE_NAME) AS PURCHASE_TYPE"
                        + " FROM ICM_BID b"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON b.ICM_PR_SEQ = pr.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pt.ICM_PC_TYPE_SEQ = pr.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE ru ON pr.ICM_PC_RULE_SEQ = ru.ICM_PC_RULE_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pm.ICM_PC_METHOD_SEQ = pr.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON pr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON b.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE b.ICM_BID_SEQ = @bid_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                    SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                    MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    MasterPurchaseMethodInfo infoPurchaseMethod = new MasterPurchaseMethodInfo();

                    infoYearType.Seq = (Convert.IsDBNull(dr[0]["PR_YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["PR_YEAR_TYPE_SEQ"]));
                    infoYearType.Name = dr[0]["YEAR_TYPE_NAME"].ToString();
                    infoPurchaseRule.Seq = (Convert.IsDBNull(dr[0]["PR_ICM_PC_RULE_SEQ"]) ? null : (Nullable<int>)(dr[0]["PR_ICM_PC_RULE_SEQ"]));
                    infoPurchaseRule.Name = (dr[0]["ICM_PC_RULE_NAME"]).ToString();
                    infoPr.Name = (dr[0]["ICM_PR_NAME"]).ToString();
                    infoPr.Ref_No = (dr[0]["ICM_PR_REF_NO"]).ToString();
                    infoPr.BgmYear = (Convert.IsDBNull(dr[0]["PR_BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["PR_BGM_YEAR"]));
                    infoPr.PurchaseType = (dr[0]["PURCHASE_TYPE"]).ToString();
                    infoPr.InfoPurchaseRule = infoPurchaseRule;
                    infoPr.InfoYearType = infoYearType;

                    info.Seq = Convert.ToInt32(dr[0]["ICM_BID_SEQ"]);
                    info.Ref_no = (dr[0]["ICM_BID_REF_NO"]).ToString();
                    info.Name = (dr[0]["ICM_BID_NAME"]).ToString();
                    info.Descr = (dr[0]["ICM_BID_DESCR"]).ToString();
                    info.ReceiveType = (dr[0]["RECEIVE_TYPE"]).ToString();
                    info.DocumentAmount = (Convert.IsDBNull(dr[0]["DOCUMENT_AMOUNT"]) ? null : (Nullable<int>)(dr[0]["DOCUMENT_AMOUNT"]));
                    info.PlaceReceive = (dr[0]["RECEIVE_PLACE"]).ToString();
                    info.PlaceOffer = (dr[0]["OFFER_PLACE"]).ToString();
                    info.DateBid = (Convert.IsDBNull(dr[0]["BID_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["BID_DATE"]));
                    info.DateAnnounce = (Convert.IsDBNull(dr[0]["ANNOUNCE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["ANNOUNCE_DATE"]));
                    info.DateStartReceive = (Convert.IsDBNull(dr[0]["RECEIVE_DATE_START"]) ? null : (Nullable<DateTime>)(dr[0]["RECEIVE_DATE_START"]));
                    info.DateEndReceive = (Convert.IsDBNull(dr[0]["RECEIVE_DATE_END"]) ? null : (Nullable<DateTime>)(dr[0]["RECEIVE_DATE_END"]));
                    info.DateBuilding = (Convert.IsDBNull(dr[0]["BUILDING_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["BUILDING_DATE"]));
                    info.DateExplain = (Convert.IsDBNull(dr[0]["EXPLAIN_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["EXPLAIN_DATE"]));
                    info.DateStartOffer = (Convert.IsDBNull(dr[0]["OFFER_DATE_START"]) ? null : (Nullable<DateTime>)(dr[0]["OFFER_DATE_START"]));
                    info.DateEndOffer = (Convert.IsDBNull(dr[0]["OFFER_DATE_END"]) ? null : (Nullable<DateTime>)(dr[0]["OFFER_DATE_END"]));
                    info.DateDisclose = (Convert.IsDBNull(dr[0]["DISCLOSE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["DISCLOSE_DATE"]));
                    info.TimeStartReceive = (Convert.IsDBNull(dr[0]["RECEIVE_TIME_START"]) ? null : (Nullable<TimeSpan>)(dr[0]["RECEIVE_TIME_START"]));
                    info.TimeEndReceive = (Convert.IsDBNull(dr[0]["RECEIVE_TIME_END"]) ? null : (Nullable<TimeSpan>)(dr[0]["RECEIVE_TIME_END"]));
                    info.TimeStartBuilding = (Convert.IsDBNull(dr[0]["BUILDING_TIME_START"]) ? null : (Nullable<TimeSpan>)(dr[0]["BUILDING_TIME_START"]));
                    info.TimeEndBuilding = (Convert.IsDBNull(dr[0]["BUILDING_TIME_END"]) ? null : (Nullable<TimeSpan>)(dr[0]["BUILDING_TIME_END"]));
                    info.TimeExplain = (Convert.IsDBNull(dr[0]["EXPLAIN_TIME"]) ? null : (Nullable<TimeSpan>)(dr[0]["EXPLAIN_TIME"]));
                    info.TimeStartOffer = (Convert.IsDBNull(dr[0]["OFFER_TIME_START"]) ? null : (Nullable<TimeSpan>)(dr[0]["OFFER_TIME_START"]));
                    info.TimeEndOffer = (Convert.IsDBNull(dr[0]["OFFER_TIME_END"]) ? null : (Nullable<TimeSpan>)(dr[0]["OFFER_TIME_END"]));
                    info.TimeDisclose = (Convert.IsDBNull(dr[0]["DISCLOSE_TIME"]) ? null : (Nullable<TimeSpan>)(dr[0]["DISCLOSE_TIME"]));

                    infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoStatus.Name = (dr[0]["RECORD_STATUS_NAME"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));

                    info.InfoPr = infoPr;
                    infoRecord.InfoStatus = infoStatus;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(BidInfo info, IList<PurchaseRequestCommitteeInfo> infoListCommittee, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            BidCommitteeDAL dalCommittee = new BidCommitteeDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(31);

                dbManager.AddParameters(0, "@year_type", info.InfoYearType.Seq);
                dbManager.AddParameters(1, "@year", info.Year);
                dbManager.AddParameters(2, "@ref_no", info.Ref_no);
                dbManager.AddParameters(3, "@name", info.Name);
                dbManager.AddParameters(4, "@pr_seq", info.InfoPr.Seq);
                dbManager.AddParameters(5, "@date_bid", info.DateBid);
                dbManager.AddParameters(6, "@emp_bid", info.InfoEmployeeBid.Seq);
                dbManager.AddParameters(7, "@job_site_bid", info.InfoJobSiteBid.Seq);
                dbManager.AddParameters(8, "@date_announce", info.DateAnnounce);
                dbManager.AddParameters(9, "@date_start_receive", info.DateStartReceive);
                dbManager.AddParameters(10, "@date_end_receive", info.DateEndReceive);
                dbManager.AddParameters(11, "@date_building", info.DateBuilding);
                dbManager.AddParameters(12, "@date_explain", info.DateExplain);
                dbManager.AddParameters(13, "@date_start_offer", info.DateStartOffer);
                dbManager.AddParameters(14, "@date_end_offer", info.DateEndOffer);
                dbManager.AddParameters(15, "@date_disclose", info.DateDisclose);
                dbManager.AddParameters(16, "@time_start_receive", info.TimeStartReceive);
                dbManager.AddParameters(17, "@time_end_receive", info.TimeEndReceive);
                dbManager.AddParameters(18, "@time_start_building", info.TimeStartBuilding);
                dbManager.AddParameters(19, "@time_end_building", info.TimeEndBuilding);
                dbManager.AddParameters(20, "@time_explain", info.TimeExplain);
                dbManager.AddParameters(21, "@time_start_offer", info.TimeStartOffer);
                dbManager.AddParameters(22, "@time_end_offer", info.TimeEndOffer);
                dbManager.AddParameters(23, "@time_disclose", info.TimeDisclose);
                dbManager.AddParameters(24, "@place_receive", info.PlaceReceive);
                dbManager.AddParameters(25, "@place_offer", info.PlaceOffer);
                dbManager.AddParameters(26, "@receive_type", info.ReceiveType);
                dbManager.AddParameters(27, "@document_amount", info.DocumentAmount);
                dbManager.AddParameters(28, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(29, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(30, "@created_when", info.InfoRecord.Created_when);


                strSQL = "INSERT INTO ICM_BID"
                    + " (YEAR_TYPE_SEQ, BGM_YEAR, ICM_BID_REF_NO, ICM_BID_NAME, ICM_PR_SEQ"
                    + ", BID_DATE, BID_EMP_SEQ, BID_JOB_SITE_SEQ, ANNOUNCE_DATE"
                    + ", RECEIVE_DATE_START, RECEIVE_DATE_END, BUILDING_DATE, EXPLAIN_DATE"
                    + ", OFFER_DATE_START, OFFER_DATE_END, DISCLOSE_DATE, RECEIVE_TIME_START"
                    + ", RECEIVE_TIME_END, BUILDING_TIME_START, BUILDING_TIME_END, EXPLAIN_TIME"
                    + ", OFFER_TIME_START, OFFER_TIME_END, DISCLOSE_TIME, RECEIVE_PLACE, OFFER_PLACE"
                    + ", RECEIVE_TYPE, DOCUMENT_AMOUNT"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@year_type"
                    + ", @year"
                    + ", @ref_no"
                    + ", @name"
                    + ", @pr_seq"
                    + ", @date_bid"
                    + ", @emp_bid"
                    + ", @job_site_bid"
                    + ", @date_announce"
                    + ", @date_start_receive"
                    + ", @date_end_receive"
                    + ", @date_building"
                    + ", @date_explain"
                    + ", @date_start_offer"
                    + ", @date_end_offer"
                    + ", @date_disclose"
                    + ", @time_start_receive"
                    + ", @time_end_receive"
                    + ", @time_start_building"
                    + ", @time_end_building"
                    + ", @time_explain"
                    + ", @time_start_offer"
                    + ", @time_end_offer"
                    + ", @time_disclose"
                    + ", @place_receive"
                    + ", @place_offer"
                    + ", @receive_type"
                    + ", @document_amount"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_BID";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                dalCommittee.Insert(infoListCommittee, lastSeq, dbManager);
                dalDocument.InsertFileToDB(infoListFile, "ICM_BID", "ICM_BID_SEQ", lastSeq, dbManager);

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(BidInfo info)
        {
            string strSQL;
            DataSet ds;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(28);

                dbManager.AddParameters(0, "@bid_seq", info.Seq);
                dbManager.AddParameters(1, "@ref_no", info.Ref_no);
                dbManager.AddParameters(2, "@name", info.Name);
                dbManager.AddParameters(3, "@date_bid", info.DateBid);
                dbManager.AddParameters(4, "@date_announce", info.DateAnnounce);
                dbManager.AddParameters(5, "@date_start_receive", info.DateStartReceive);
                dbManager.AddParameters(6, "@date_end_receive", info.DateEndReceive);
                dbManager.AddParameters(7, "@date_building", info.DateBuilding);
                dbManager.AddParameters(8, "@date_explain", info.DateExplain);
                dbManager.AddParameters(9, "@date_start_offer", info.DateStartOffer);
                dbManager.AddParameters(10, "@date_end_offer", info.DateEndOffer);
                dbManager.AddParameters(11, "@date_disclose", info.DateDisclose);
                dbManager.AddParameters(12, "@time_start_receive", info.TimeStartReceive);
                dbManager.AddParameters(13, "@time_end_receive", info.TimeEndReceive);
                dbManager.AddParameters(14, "@time_start_building", info.TimeStartBuilding);
                dbManager.AddParameters(15, "@time_end_building", info.TimeEndBuilding);
                dbManager.AddParameters(16, "@time_explain", info.TimeExplain);
                dbManager.AddParameters(17, "@time_start_offer", info.TimeStartOffer);
                dbManager.AddParameters(18, "@time_end_offer", info.TimeEndOffer);
                dbManager.AddParameters(19, "@time_disclose", info.TimeDisclose);
                dbManager.AddParameters(20, "@place_receive", info.PlaceReceive);
                dbManager.AddParameters(21, "@place_offer", info.PlaceOffer);
                dbManager.AddParameters(22, "@receive_type", info.ReceiveType);
                dbManager.AddParameters(23, "@document_amount", info.DocumentAmount);
                dbManager.AddParameters(24, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(25, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(26, "@updated_when", info.InfoRecord.Updated_when);
                dbManager.AddParameters(27, "@descr", info.Descr);

                strSQL = "UPDATE ICM_BID"
                    + " SET ICM_BID_REF_NO = @ref_no"
                    + ", ICM_BID_NAME = @name"
                    + ", ICM_BID_DESCR = @descr"
                    + ", BID_DATE = @date_bid"
                    + ", ANNOUNCE_DATE = @date_announce"
                    + ", RECEIVE_DATE_START =@date_start_receive"
                    + ", RECEIVE_DATE_END = @date_end_receive"
                    + ", BUILDING_DATE = @date_building"
                    + ", EXPLAIN_DATE = @date_explain"
                    + ", OFFER_DATE_START = @date_start_offer"
                    + ", OFFER_DATE_END = @date_end_offer"
                    + ", DISCLOSE_DATE = @date_disclose"
                    + ", RECEIVE_TIME_START = @time_start_receive"
                    + ", RECEIVE_TIME_END = @time_end_receive"
                    + ", BUILDING_TIME_START = @time_start_building"
                    + ", BUILDING_TIME_END = @time_end_building"
                    + ", EXPLAIN_TIME = @time_explain"
                    + ", OFFER_TIME_START = @time_start_offer"
                    + ", OFFER_TIME_END = @time_end_offer"
                    + ", DISCLOSE_TIME = @time_disclose"
                    + ", RECEIVE_PLACE = @place_receive"
                    + ", OFFER_PLACE = @place_offer"
                    + ", RECEIVE_TYPE = @receive_type"
                    + ", DOCUMENT_AMOUNT = @document_amount"
                    + ", RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_BID_SEQ = @bid_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<BidInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_BID"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_BID_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string UpdateTotalAndAmountConfirm(BidInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<BidRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmountForBid(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoneyForBid(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "1")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบวงเงินและจำนวน";
                }

                Confirm(info, infoListRoute, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return returnTxt;
        }

        public void Confirm(BidInfo info, IList<BidRouteInfo> infoListRoute, IDBManager dbManagerTrans)
        {
            string strSQL;
            BidRouteDAL dalRoute = new BidRouteDAL();
            try
            {
                dbManagerTrans.CreateParameters(4);

                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@confirm_emp_seq", info.InfoEmployeeConfirmed.Seq);
                dbManagerTrans.AddParameters(2, "@confirm_date", info.DateConfirmed);
                dbManagerTrans.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);// SystemInfo._ProcessRecordStatus);

                strSQL = "UPDATE ICM_BID"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_BID_SEQ = @seq";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoEmployeeConfirmed.Seq, dbManagerTrans);
            }
            catch
            {
                throw;
            }
        }
    }
}
